package cn.taobaoke.restful.domain;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Optional;

/**
 * @author yangzuo
 * @since 2017-03-21
 * TODO Test the correctness of SQL
 */
public interface WxGroupRepository extends JpaRepository<WxGroupEntity, Long> {

    List<WxGroupEntity> findAllByDomainId(Long domainId);

    Optional<WxGroupEntity> findFirstByNameAndDomainId(String name, Long domainId);

    @Query(value = "select wx.* from tb_wx_group wx where wx.id in "
            + "(select relation.group_id from tb_agent_group_relation relation "
            + "left join tb_agent agent on relation.agent_id = agent.id where agent.id = :id) and wx.domain_id = :domainId",
            nativeQuery = true)
    List<WxGroupEntity> findGroupsForAgent(@Param("id") Long agentId, @Param("domainId") Long domainId);

    @Query(value = "select wx.* from tb_agent_group_relation relation "
            + "left join tb_agent agent on relation.agent_id = agent.id "
            + "left join tb_wx_group wx on relation.group_id = wx.id "
            + "where agent.id = :agentId and wx.id = :groupId and wx.domain_id = :domainId", nativeQuery = true)
    Optional<WxGroupEntity> findGroupForAgentByAgentIdAndGroupId(@Param("agentId") Long agentId, @Param("groupId") Long groupId, @Param("domainId") Long domainId);

    @Query("from WxGroupEntity wx where wx.id = :id and wx.isBelongToAdmin = :flag and wx.domainId = :domainId")
    Optional<WxGroupEntity> findByGroupId(@Param("id") Long groupId, @Param("flag") Boolean isBelongToAdmin, @Param("domainId") Long domainId);

    @Query("from WxGroupEntity wx where wx.name = :name and wx.isBelongToAdmin = :flag and wx.domainId = :domainId")
    Optional<WxGroupEntity> findByName(@Param("name") String groupName, @Param("flag") Boolean isBelongToAdmin, @Param("domainId") Long domainId);

    List<WxGroupEntity> findByIsBelongToAdminAndDomainId(Boolean isBelongToAdmin, Long domainId);

    Optional<WxGroupEntity> findByIdAndIsBelongToAdminAndDomainId(Long id, Boolean isBelongToAdmin, Long domainId);

    @Query("from WxGroupEntity wx where wx.id in (select relation.groupId from AgentGroupRelation relation where relation.agentId = :id) and wx.domainId = :domainId")
    List<WxGroupEntity> findWxGroupListByAgentId(@Param("id") Long agentId, @Param("domainId") Long domainId);

    @Query(value = "SELECT pid FROM tb_agent WHERE id = (SELECT r.agent_id FROM tb_wx_group wx, tb_agent_group_relation r WHERE wx.name = :name AND wx.id = r.group_id AND wx.domain_id = :domainId)", nativeQuery = true)
    Optional<String> findWxGroupPidByName(@Param("name") String name, @Param("domainId") Long domainId);
}
